﻿package com.dbm.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.dbm.model.ConnPoolFactory;
import com.dbm.model.OracleFunctionUtil;
import com.dbm.util.PackageInfo;
import com.dbm.util.PackageItem;
import com.dbm.util.SellSoInfoUtil;


/**
 * 发货单数据处理dao
 * @author wqying
 *
 */
public class CtlSellSoInfoDao {
	Logger log = Logger
			.getLogger(CtlSellSoInfoDao.class);
	private CtlPublicDao ctlPublicDao = new CtlPublicDao();
	private OwnerCodeDao ownerCodeDao = new OwnerCodeDao();
	
	/**
	 * 新增包裹内商品明细
	 * @param soInfo
	 * @param id
	 * @param packageid
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean addPackAgeItem(SellSoInfoUtil soInfo,String id,String packageid) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPackItemSql = "insert into TPACKAGEITEM (" +
				"ID, PACKAGEID, ITEMCODE, ITEMID, QUANTITY, " +
				"OWNERCODE)"+
				"values (?, ?, ?, ?, ?, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPackItemSql);
		ctlStat.setString(1, id);
		ctlStat.setString(2, packageid);
		ctlStat.setString(3, soInfo.getITEMCODE());
		ctlStat.setString(4, soInfo.getITEMID());
		ctlStat.setBigDecimal(5, soInfo.getACTUALQTY());
		ctlStat.setString(6, soInfo.getOWNERCODE());
		if(ctlStat.executeUpdate()==1){
			flag = true;
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	/**
	 * 新增包裹内商品明细---多包裹
	 * @param PackageItem
	 * @param id
	 * @param packageid
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean addPackAgeItem(PackageItem packageItem,String id,String packageid) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPackItemSql = "insert into TPACKAGEITEM (" +
				"ID, PACKAGEID, ITEMCODE, ITEMID, QUANTITY, " +
				"OWNERCODE)"+
				"values (?, ?, ?, ?, ?, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPackItemSql);
		ctlStat.setString(1, id);
		ctlStat.setString(2, packageid);
		ctlStat.setString(3, packageItem.getITEMCODE());
		ctlStat.setString(4, packageItem.getITEMID());
		ctlStat.setBigDecimal(5, packageItem.getQUANTITY());
		ctlStat.setString(6, packageItem.getOWNERCODE());
		if(ctlStat.executeUpdate()==1){
			flag = true;
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	/**
	 * 将发货数据写入 packageinfo表
	 * @param soInfo
	 * @return
	 */
	public synchronized boolean addPackageInfo(PackageInfo packageInfo,String packageId,String deliveryOrderCodeId) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPageSql = "insert into TPACKAGEINFO " +
				"(DELIVERYORDERCODEID, LOGISTICSCODE, LOGISTICSNAME, EXPRESSCODE, PACKAGECODE, " +
				"LENGTH, WIDTH, HEIGHT, WEIGHT, VOLUME, " +
				"INVOICENO, THEORETICALWEIGHT, PACKAGEID)"
				+"values (?, ?, ?, ?, ?," +
				" 0.00, 0.00, 0.00, ?, 0.000, " +
				"?, 0, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPageSql);
		ctlStat.setString(1, deliveryOrderCodeId);
		ctlStat.setString(2, packageInfo.getLOGISTICSCODE());
		ctlStat.setString(3, packageInfo.getLOGISTICSNAME());
		ctlStat.setString(4, packageInfo.getEXPRESSCODE());
		ctlStat.setString(5, packageId);
		ctlStat.setBigDecimal(6, packageInfo.getWEIGHT());
		ctlStat.setString(7, packageInfo.getINVOICENO());
		ctlStat.setString(8, packageId);
		log.info("insert into TPACKAGEINFO (DELIVERYORDERCODEID, LOGISTICSCODE, LOGISTICSNAME, EXPRESSCODE, PACKAGECODE, " +
				"LENGTH, WIDTH, HEIGHT, WEIGHT, VOLUME, INVOICENO, THEORETICALWEIGHT, PACKAGEID) " +
				"values ("+deliveryOrderCodeId+", "
				+packageInfo.getLOGISTICSCODE()+", "
				+packageInfo.getLOGISTICSNAME()+", "
				+packageInfo.getEXPRESSCODE()+", "
				+packageId+",0.00, 0.00, 0.00, "+packageInfo.getWEIGHT()+", 0.000,"+packageInfo.getINVOICENO()+", 0, "+packageId+")");
		if(ctlStat.executeUpdate()==1){
			flag = true;
			log.info("新增packageInfo数据成功，id为"+packageId);
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	/**
	 * 将发货数据写入 packageinfo表
	 * @param soInfo
	 * @return
	 */
	public synchronized boolean addPackageInfo(SellSoInfoUtil soInfo,String packageId,String deliveryOrderCodeId) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPageSql = "insert into TPACKAGEINFO " +
				"(DELIVERYORDERCODEID, LOGISTICSCODE, LOGISTICSNAME, EXPRESSCODE, PACKAGECODE, " +
				"LENGTH, WIDTH, HEIGHT, WEIGHT, VOLUME, " +
				"INVOICENO, THEORETICALWEIGHT, PACKAGEID)"
				+"values (?, ?, ?, ?, ?," +
				" 0.00, 0.00, 0.00, 0.000, 0.000, " +
				"?, 0, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPageSql);
		ctlStat.setString(1, deliveryOrderCodeId);
		ctlStat.setString(2, soInfo.getLOGISTICSCODE());
		ctlStat.setString(3, soInfo.getLOGISTICSNAME());
		ctlStat.setString(4, soInfo.getEXPRESSCODE());
		ctlStat.setString(5, soInfo.getPACKAGECODE());
		ctlStat.setBigDecimal(6, soInfo.getTHEORETICALWEIGHT());
		ctlStat.setString(7, packageId);
		if(ctlStat.executeUpdate()==1){
			flag = true;
			log.info("新增packageInfo数据成功，id为"+packageId);
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	/**
	 * 通过出库单号，获取出库Id
	 * @param soreference1
	 * @return
	 * @throws Exception
	 */
	public synchronized String getdeliveryOrderId(String soreference1) throws Exception{
		String result = "";
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String sql = "SELECT t.deliveryOrderId from tSellDeliveryOrderInfo t where t.deliveryOrderCode=? and t.destination='WMS' and t.sendflag='SEND'";
		PreparedStatement ctlstat = ctlconn.prepareStatement(sql);
		ctlstat.setString(1, soreference1);
		ResultSet rs = ctlstat.executeQuery();
		if(rs.next()){
			result = rs.getString("deliveryOrderId");
		}
		rs.close();
		ctlstat.close();
		ctlconn.close();
		return result;
	}
	/**
	 * 发货单确认---头信息输入
	 * @param sellInfo
	 * @param deliveryOrderId
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean addCtlSoFhdHeader(SellSoInfoUtil sellInfo,String deliveryOrderId) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addSellSoHeaderSql = "insert into tSellDeliveryOrderInfo " +
				"(DELIVERYORDERID, DELIVERYORDERCODE, PREDELIVERYORDERCODE, PREDELIVERYORDERID, ORDERTYPE, " +
				"WAREHOUSECODE, ORDERFLAG, STATUS, SENDFLAG, DESTINATION, " +
				"OUTBIZCODE, CONFIRMTYPE, ORDERCONFIRMTIME, SOURCEPLATFORMCODE, SOURCEPLATFORMNAME, " +
				"CREATETIME, PLACEORDERTIME, PAYTIME, PAYNO, OPERATORCODE, " +
				"OPERATORNAME, OPERATETIME, SHOPNICK, SELLERNICK, BUYERNICK, " +
				"TOTALAMOUNT, ITEMAMOUNT, DISCOUNTAMOUNT, FREIGHT, ARAMOUNT, " +
				"GOTAMOUNT, SERVICEFEE, LOGISTICSCODE, LOGISTICSNAME, EXPRESSCODE, " +
				"LOGISTICSAREACODE, SCHEDULETYPE, SCHEDULEDAY, SCHEDULESTARTTIME, SCHEDULEENDTIME, " +
				"DELIVERYTYPE, SENDERINFOID, RECEIVERINFOID, INVOICEINFOID, INSURANCEFLAG, " +
				"TYPE, AMOUNT, BUYERMESSAGE, SELLERMESSAGE, REMARK)"
				+"values (?,?,?,?,?," +	"?,?,?,?,?, " +	"?,?,?,?,?, " +	"?,?,?,?,?, " +	"?,?,?,?,?," +
				"?,?,?,?,?," +"?,?,?,?,?," +"?,?,?,?,?," +"?,?,?,?,?," +"?,?,?,?,?)";
		PreparedStatement ctlstat = ctlconn.prepareStatement(addSellSoHeaderSql);
		ctlstat.setString(1, deliveryOrderId);
		ctlstat.setString(2, sellInfo.getDELIVERYORDERCODE());
		ctlstat.setString(3, sellInfo.getPREDELIVERYORDERCODE());
		ctlstat.setString(4, sellInfo.getPREDELIVERYORDERID());
		ctlstat.setString(5, sellInfo.getORDERTYPE());
		ctlstat.setString(6, sellInfo.getWAREHOUSECODE());
		ctlstat.setString(7, sellInfo.getORDERFLAG());
		ctlstat.setString(8, sellInfo.getSTATUS());
		ctlstat.setString(9, sellInfo.getSENDFLAG());
		ctlstat.setString(10, sellInfo.getDESTINATION());
		ctlstat.setString(11, sellInfo.getOUTBIZCODE());
		ctlstat.setBigDecimal(12, sellInfo.getCONFIRMTYPE());
		ctlstat.setString(13, sellInfo.getORDERCONFIRMTIME());
		ctlstat.setString(14, sellInfo.getSOURCEPLATFORMCODE());
		ctlstat.setString(15, sellInfo.getSOURCEPLATFORMNAME());
		ctlstat.setString(16, sellInfo.getCREATETIME());
		ctlstat.setString(17, sellInfo.getPLACEORDERTIME());
		ctlstat.setString(18, sellInfo.getPAYTIME());
		ctlstat.setString(19, sellInfo.getPAYNO());
		ctlstat.setString(20, sellInfo.getOPERATORCODE());
		ctlstat.setString(21, sellInfo.getOPERATORNAME());
		ctlstat.setString(22, sellInfo.getOPERATETIME());
		ctlstat.setString(23, sellInfo.getSHOPNICK());
		ctlstat.setString(24, sellInfo.getSELLERNICK());
		ctlstat.setString(25, sellInfo.getBUYERNICK());
		ctlstat.setBigDecimal(26, sellInfo.getTOTALAMOUNT());
		ctlstat.setBigDecimal(27, sellInfo.getITEMAMOUNT());
		ctlstat.setBigDecimal(28, sellInfo.getDISCOUNTAMOUNT());
		ctlstat.setBigDecimal(29, sellInfo.getFREIGHT());
		ctlstat.setBigDecimal(30, sellInfo.getARAMOUNT());
		ctlstat.setBigDecimal(31, sellInfo.getGOTAMOUNT());
		ctlstat.setBigDecimal(32, sellInfo.getSERVICEFEE());
		ctlstat.setString(33, sellInfo.getLOGISTICSCODE());
		ctlstat.setString(34, sellInfo.getLOGISTICSNAME());
		ctlstat.setString(35, sellInfo.getEXPRESSCODE());
		ctlstat.setString(36, sellInfo.getLOGISTICSAREACODE());
		ctlstat.setBigDecimal(37, sellInfo.getSCHEDULETYPE());
		ctlstat.setString(38, sellInfo.getSCHEDULEDAY());
		ctlstat.setString(39, sellInfo.getSCHEDULESTARTTIME());
		ctlstat.setString(40, sellInfo.getSCHEDULEENDTIME());
		ctlstat.setString(41, sellInfo.getDELIVERYTYPE());
		ctlstat.setString(42, sellInfo.getSENDERINFOID());
		ctlstat.setString(43, sellInfo.getRECEIVERINFOID());
		ctlstat.setString(44, sellInfo.getINVOICEINFOID());
		ctlstat.setString(45, sellInfo.getINSURANCEFLAG());
		ctlstat.setString(46, sellInfo.getTYPE());
		ctlstat.setBigDecimal(47, sellInfo.getAMOUNT());
		ctlstat.setString(48, sellInfo.getBUYERMESSAGE());
		ctlstat.setString(49, sellInfo.getSELLERMESSAGE());
		ctlstat.setString(50, sellInfo.getREMARK());
		if(ctlstat.executeUpdate()==1){
			flag = true;
		}
		ctlstat.close();
		ctlconn.close();
		return flag;
	}
	/**
	 * 发货单确认---明细信息输入
	 * @param sellInfo
	 * @param deliveryOrderId
	 * @return
	 * @throws Exception
	 */
	public synchronized void addSellDetails(List<SellSoInfoUtil> sellInfos,String deliveryOrderId) throws Exception{
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		for(SellSoInfoUtil sellInfo:sellInfos){
			String addSellSoDetailSql = "insert into TSELLDELIVERYORDERLINES "
					+ "(ORDERLINEID, DELIVERYORDERID, ORDERLINENO, SOURCEORDERCODE, SUBSOURCEORDERCODE,"
					+ " PAYNO, OWNERCODE, ITEMCODE, ITEMID, INVENTORYTYPE, "
					+ "ITEMNAME, EXTCODE, RETAILPRICE, ACTUALPRICE, DISCOUNTAMOUNT, "
					+ "BATCHCODE, PRODUCTDATE, EXPIREDATE, ACTUALQTY, PLANQTY, "
					+ "PRODUCECODE)" + "values (?, ?, ?, ?, ?, " + "?,?,?,?,?,"
					+ "?,?,?,?,?," + "?,?,?,?,?," + "?)";
			PreparedStatement ctlstat = ctlconn
					.prepareStatement(addSellSoDetailSql);
			System.out.println(addSellSoDetailSql);
			ctlstat.setString(1, ctlPublicDao.getEntryOrderIdBySeq("seqSELLDELIVERYORDERLINES"));
			ctlstat.setString(2, deliveryOrderId);
			ctlstat.setString(3, sellInfo.getORDERLINENO());
			ctlstat.setString(4, sellInfo.getSOURCEORDERCODE());
			ctlstat.setString(5, sellInfo.getSUBSOURCEORDERCODE());
			ctlstat.setString(6, sellInfo.getPAYNO());
			ctlstat.setString(7, sellInfo.getOWNERCODE());
			ctlstat.setString(8, sellInfo.getITEMCODE());
			ctlstat.setString(9, sellInfo.getITEMID());
			ctlstat.setString(10, sellInfo.getINVENTORYTYPE());
			ctlstat.setString(11, sellInfo.getITEMNAME());
			ctlstat.setString(12, sellInfo.getEXTCODE());
			ctlstat.setBigDecimal(13, sellInfo.getRETAILPRICE());
			ctlstat.setBigDecimal(14, sellInfo.getACTUALPRICE());
			ctlstat.setBigDecimal(15, sellInfo.getDISCOUNTAMOUNT());
			ctlstat.setString(16, sellInfo.getBATCHCODE());
			ctlstat.setString(17, sellInfo.getPRODUCTDATE());
			ctlstat.setString(18, sellInfo.getEXPIREDATE());
			ctlstat.setBigDecimal(19, sellInfo.getACTUALQTY());
			ctlstat.setBigDecimal(20, sellInfo.getPLANQTY());
			ctlstat.setString(21, sellInfo.getPRODUCECODE());
			ctlstat.executeUpdate();
			ctlstat.close();
		}
		ctlconn.close();
	}
	
	
	
//	public synchronized 
	/**
	 * WMS 中的 SellSoInfoUtil 转换为 ctl 的sellSoInfo 信息
	 * @param sellSoInfoUtil
	 * @return
	 * @throws Exception
	 */
	public synchronized SellSoInfoUtil getSellSoInfoUtilsBySellSoInfo(SellSoInfoUtil sellSoInfoUtil) throws Exception{
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String a = OracleFunctionUtil.getSysConfiguration(sellSoInfoUtil.getWAREHOUSECODE(), sellSoInfoUtil.getOWNERCODE(), "TOP_QM_SKU");
		PreparedStatement ctlstat = null;
		if("CODE".equals(a.toUpperCase())){
		String getSellSoInfoUtilSql = "select th.DELIVERYORDERID, "+
			       "th.DELIVERYORDERCODE, "+
			       "th.PREDELIVERYORDERCODE, "+
			       "th.PREDELIVERYORDERID, "+
			       "th.ORDERTYPE, "+
			       "th.WAREHOUSECODE, "+
			       "th.ORDERFLAG, "+
			       "th.STATUS, "+
			       "th.SENDFLAG, "+
			       "th.DESTINATION, "+
			       "th.OUTBIZCODE, "+
			       "th.CONFIRMTYPE, "+
			       "th.ORDERCONFIRMTIME, "+
			       "th.SOURCEPLATFORMCODE, "+
			       "th.SOURCEPLATFORMNAME, "+
			       "th.CREATETIME, "+
			       "th.PLACEORDERTIME, "+
			       "th.PAYTIME, "+
			       "th.PAYNO, "+
			       "th.OPERATORCODE, "+
			       "th.OPERATORNAME, "+
			       "th.OPERATETIME, "+
			       "th.SHOPNICK, "+
			       "th.SELLERNICK, "+
			       "th.BUYERNICK, "+
			       "th.TOTALAMOUNT, "+
			       "th.ITEMAMOUNT, "+
			       "th.DISCOUNTAMOUNT, "+
			       "th.FREIGHT, "+
			       "th.ARAMOUNT, "+
			       "th.GOTAMOUNT, "+
			       "th.SERVICEFEE, "+
			       "th.LOGISTICSCODE, "+
			       "th.LOGISTICSNAME, "+
			       "th.EXPRESSCODE, "+
			       "th.LOGISTICSAREACODE, "+
			       "th.SCHEDULETYPE, "+
			       "th.SCHEDULEDAY, "+
			       "th.SCHEDULESTARTTIME, "+
			       "th.SCHEDULEENDTIME, "+
			       "th.DELIVERYTYPE, "+
			       "th.SENDERINFOID, "+
			       "th.RECEIVERINFOID, "+
			       "th.INVOICEINFOID, "+
			       "th.INSURANCEFLAG, "+
			       "th.TYPE, "+
			       "th.AMOUNT, "+
			       "th.BUYERMESSAGE, "+
			       "th.SELLERMESSAGE, "+
			       "th.REMARK, "+
			       "td.ORDERLINEID, "+
			       "td.ORDERLINENO, "+
			       "td.SOURCEORDERCODE, "+
			       "td.SUBSOURCEORDERCODE, "+
			       "td.PAYNO as payno_det, "+
			       "td.OWNERCODE, "+
			       "td.ITEMCODE, "+
			       "td.ITEMID, "+
			       "td.INVENTORYTYPE, "+
			       "td.ITEMNAME, "+
			       "td.EXTCODE, "+
			       "td.RETAILPRICE, "+
			       "td.ACTUALPRICE, "+
			       "td.DISCOUNTAMOUNT as skuDISCOUNTAMOUNT, "+
			       "td.BATCHCODE, "+
			       "td.PRODUCTDATE, "+
			       "td.EXPIREDATE, "+
			       "td.ACTUALQTY, "+
			       "td.PLANQTY, "+
			       "td.PRODUCECODE, "+
			       "tu.USERID, "+
			       "tu.COMPANY, "+
			       "tu.NAME, "+
			       "tu.ZIPCODE, "+
			       "tu.TEL, "+
			       "tu.MOBILE, "+
			       "tu.EMAIL, "+
			       "tu.COUNTRYCODE, "+
			       "tu.PROVINCE, "+
			       "tu.CITY, "+
			       "tu.AREA, "+
			       "tu.TOWN, "+
			       "tu.DETAILADDRESS, "+
			       "tu.CARNO, "+
			       "tu.CARDIDNO, "+
			       "tp.packagecode, "+
			       "tp.length, "+
			       "tp.width, "+
			       "tp.height, "+
			       "tp.weight, "+
			       "tp.volume, "+
			       "tp.invoiceno, "+
			       "tp.theoreticalweight "+
			  "from tSellDeliveryOrderInfo th "+
			  "left join tSellDeliveryOrderLines td "+
			  "on th.deliveryorderid = td.deliveryorderid "+
			  "left join TORDERSUSERINFO tu "+
			  "on th.receiverInfoId = tu.userid "+
			  "left join TPACKAGEINFO tp "+
			  "on tp.deliveryordercodeid = th.deliveryorderid "+
			  "left join Tpackageitem ti on tp.packageId = ti.packageid "+
			  "where th.destination = 'WMS' AND TH.WAREHOUSECODE=? AND TH.SENDFLAG='SEND' AND TH.STATUS ='ACCEPT'"+
			  " and th.DELIVERYORDERCODE=? AND td.itemcode=? and td.planqty=? order by td.orderlineno  ";
		log.info(getSellSoInfoUtilSql+"\n"+sellSoInfoUtil.getDELIVERYORDERCODE());
		ctlstat = ctlconn.prepareStatement(getSellSoInfoUtilSql);
		ctlstat.setString(1, sellSoInfoUtil.getWAREHOUSECODE());
		ctlstat.setString(2, sellSoInfoUtil.getDELIVERYORDERCODE());
		ctlstat.setString(3, sellSoInfoUtil.getITEMCODE());
		ctlstat.setBigDecimal(4,sellSoInfoUtil.getPLANQTY());
		}
		if("ID".equals(a.toUpperCase())){
			String getSellSoInfoUtilSql = "select th.DELIVERYORDERID, "+
				       "th.DELIVERYORDERCODE, "+
				       "th.PREDELIVERYORDERCODE, "+
				       "th.PREDELIVERYORDERID, "+
				       "th.ORDERTYPE, "+
				       "th.WAREHOUSECODE, "+
				       "th.ORDERFLAG, "+
				       "th.STATUS, "+
				       "th.SENDFLAG, "+
				       "th.DESTINATION, "+
				       "th.OUTBIZCODE, "+
				       "th.CONFIRMTYPE, "+
				       "th.ORDERCONFIRMTIME, "+
				       "th.SOURCEPLATFORMCODE, "+
				       "th.SOURCEPLATFORMNAME, "+
				       "th.CREATETIME, "+
				       "th.PLACEORDERTIME, "+
				       "th.PAYTIME, "+
				       "th.PAYNO, "+
				       "th.OPERATORCODE, "+
				       "th.OPERATORNAME, "+
				       "th.OPERATETIME, "+
				       "th.SHOPNICK, "+
				       "th.SELLERNICK, "+
				       "th.BUYERNICK, "+
				       "th.TOTALAMOUNT, "+
				       "th.ITEMAMOUNT, "+
				       "th.DISCOUNTAMOUNT, "+
				       "th.FREIGHT, "+
				       "th.ARAMOUNT, "+
				       "th.GOTAMOUNT, "+
				       "th.SERVICEFEE, "+
				       "th.LOGISTICSCODE, "+
				       "th.LOGISTICSNAME, "+
				       "th.EXPRESSCODE, "+
				       "th.LOGISTICSAREACODE, "+
				       "th.SCHEDULETYPE, "+
				       "th.SCHEDULEDAY, "+
				       "th.SCHEDULESTARTTIME, "+
				       "th.SCHEDULEENDTIME, "+
				       "th.DELIVERYTYPE, "+
				       "th.SENDERINFOID, "+
				       "th.RECEIVERINFOID, "+
				       "th.INVOICEINFOID, "+
				       "th.INSURANCEFLAG, "+
				       "th.TYPE, "+
				       "th.AMOUNT, "+
				       "th.BUYERMESSAGE, "+
				       "th.SELLERMESSAGE, "+
				       "th.REMARK, "+
				       "td.ORDERLINEID, "+
				       "td.ORDERLINENO, "+
				       "td.SOURCEORDERCODE, "+
				       "td.SUBSOURCEORDERCODE, "+
				       "td.PAYNO as payno_det, "+
				       "td.OWNERCODE, "+
				       "td.ITEMCODE, "+
				       "td.ITEMID, "+
				       "td.INVENTORYTYPE, "+
				       "td.ITEMNAME, "+
				       "td.EXTCODE, "+
				       "td.RETAILPRICE, "+
				       "td.ACTUALPRICE, "+
				       "td.DISCOUNTAMOUNT as skuDISCOUNTAMOUNT, "+
				       "td.BATCHCODE, "+
				       "td.PRODUCTDATE, "+
				       "td.EXPIREDATE, "+
				       "td.ACTUALQTY, "+
				       "td.PLANQTY, "+
				       "td.PRODUCECODE, "+
				       "tu.USERID, "+
				       "tu.COMPANY, "+
				       "tu.NAME, "+
				       "tu.ZIPCODE, "+
				       "tu.TEL, "+
				       "tu.MOBILE, "+
				       "tu.EMAIL, "+
				       "tu.COUNTRYCODE, "+
				       "tu.PROVINCE, "+
				       "tu.CITY, "+
				       "tu.AREA, "+
				       "tu.TOWN, "+
				       "tu.DETAILADDRESS, "+
				       "tu.CARNO, "+
				       "tu.CARDIDNO, "+
				       "tp.packagecode, "+
				       "tp.length, "+
				       "tp.width, "+
				       "tp.height, "+
				       "tp.weight, "+
				       "tp.volume, "+
				       "tp.invoiceno, "+
				       "tp.theoreticalweight "+
				  "from tSellDeliveryOrderInfo th "+
				  "left join tSellDeliveryOrderLines td "+
				  "on th.deliveryorderid = td.deliveryorderid "+
				  "left join TORDERSUSERINFO tu "+
				  "on th.receiverInfoId = tu.userid "+
				  "left join TPACKAGEINFO tp "+
				  "on tp.deliveryordercodeid = th.deliveryorderid "+
				  "left join Tpackageitem ti on tp.packageId = ti.packageid "+
				  "where th.destination = 'WMS' AND TH.WAREHOUSECODE=? AND TH.SENDFLAG='SEND' AND TH.STATUS ='ACCEPT' "+
				  "and th.DELIVERYORDERCODE=? AND td.itemid=? and td.planqty=? order by td.orderlineno  ";
			log.info(getSellSoInfoUtilSql+"\n"+sellSoInfoUtil.getDELIVERYORDERCODE());
			ctlstat = ctlconn.prepareStatement(getSellSoInfoUtilSql);
			ctlstat.setString(1, sellSoInfoUtil.getDELIVERYORDERCODE());
			ctlstat.setString(2, sellSoInfoUtil.getITEMID());
			ctlstat.setBigDecimal(3,sellSoInfoUtil.getPLANQTY());	
		}
		ResultSet rs = ctlstat.executeQuery();
		SellSoInfoUtil sellUtil = new SellSoInfoUtil();
		
		if(rs.next()){
			sellUtil.setACTUALPRICE(rs.getBigDecimal("aCTUALPRICE"));
			if(sellSoInfoUtil.getPLANQTY()==null){
				sellSoInfoUtil.setPLANQTY(new BigDecimal(0));
			}
			if(sellSoInfoUtil.getPLANQTY().equals(rs.getBigDecimal("PLANQTY"))&&sellSoInfoUtil.getITEMCODE().equals(rs.getString("ITEMCODE"))){
				sellUtil.setACTUALQTY(sellSoInfoUtil.getACTUALQTY());
			}else{
				sellUtil.setACTUALQTY(rs.getBigDecimal("ACTUALQTY"));
			}
			BigDecimal amount = rs.getBigDecimal("aMOUNT");
			log.info("数据库获取到的 amount"+amount);
			sellUtil.setAMOUNT(amount);
			sellUtil.setARAMOUNT(rs.getBigDecimal("aRAMOUNT"));
			sellUtil.setBATCHCODE(rs.getString("bATCHCODE"));
			sellUtil.setBUYERMESSAGE(rs.getString("bUYERMESSAGE"));
			sellUtil.setBUYERNICK(rs.getString("bUYERNICK"));
			sellUtil.setCONFIRMTYPE(rs.getBigDecimal("cONFIRMTYPE"));
			sellUtil.setCREATETIME(rs.getString("cREATETIME"));
			sellUtil.setDELIVERYORDERCODE(rs.getString("dELIVERYORDERCODE"));
			sellUtil.setDELIVERYORDERID(rs.getString("dELIVERYORDERID"));
			sellUtil.setDELIVERYTYPE(rs.getString("dELIVERYTYPE"));
			sellUtil.setDESTINATION(rs.getString("dESTINATION"));
			sellUtil.setDISCOUNTAMOUNT(rs.getBigDecimal("dISCOUNTAMOUNT"));
			sellUtil.setEXPIREDATE(rs.getString("eXPIREDATE"));
			sellUtil.setEXPRESSCODE(rs.getString("eXPRESSCODE"));
			sellUtil.setEXTCODE(rs.getString("eXTCODE"));
			sellUtil.setFREIGHT(rs.getBigDecimal("fREIGHT"));
			sellUtil.setGOTAMOUNT(rs.getBigDecimal("gOTAMOUNT"));
			sellUtil.setINSURANCEFLAG(rs.getString("iNSURANCEFLAG"));
			sellUtil.setINVENTORYTYPE(rs.getString("iNVENTORYTYPE"));
			sellUtil.setINVOICEINFOID(rs.getString("iNVOICEINFOID"));
			sellUtil.setITEMAMOUNT(rs.getBigDecimal("iTEMAMOUNT"));
			sellUtil.setITEMCODE(rs.getString("iTEMCODE"));
			sellUtil.setITEMID(rs.getString("iTEMID"));
			sellUtil.setITEMNAME(rs.getString("iTEMNAME"));
			sellUtil.setLOGISTICSAREACODE(rs.getString("lOGISTICSAREACODE"));
			sellUtil.setLOGISTICSCODE(rs.getString("lOGISTICSCODE"));
			sellUtil.setLOGISTICSNAME(rs.getString("lOGISTICSNAME"));
			sellUtil.setOPERATETIME(rs.getString("oPERATETIME"));
			sellUtil.setOPERATORCODE(rs.getString("oPERATORCODE"));
			sellUtil.setOPERATORNAME(rs.getString("oPERATORNAME"));
			sellUtil.setORDERCONFIRMTIME(rs.getString("oRDERCONFIRMTIME"));
			sellUtil.setORDERFLAG(rs.getString("oRDERFLAG"));
			sellUtil.setORDERLINEID(rs.getString("oRDERLINEID"));
			sellUtil.setORDERLINENO(rs.getString("oRDERLINENO"));
			sellUtil.setORDERTYPE(rs.getString("oRDERTYPE"));
			sellUtil.setOUTBIZCODE(rs.getString("oUTBIZCODE"));
			sellUtil.setOWNERCODE(ownerCodeDao.getOwnerCodeByCustomerId(rs.getString("oWNERCODE")));
			sellUtil.setPAYNO(rs.getString("pAYNO"));
			sellUtil.setPAYNO_DET(rs.getString("pAYNO_DET"));
			sellUtil.setPAYTIME(rs.getString("pAYTIME"));
			sellUtil.setPLACEORDERTIME(rs.getString("pLACEORDERTIME"));
			sellUtil.setPLANQTY(rs.getBigDecimal("pLANQTY"));
			sellUtil.setPREDELIVERYORDERCODE(rs.getString("pREDELIVERYORDERCODE"));
			sellUtil.setPREDELIVERYORDERID(rs.getString("pREDELIVERYORDERID"));
			sellUtil.setPRODUCECODE(rs.getString("pRODUCECODE"));
			sellUtil.setPRODUCTDATE(rs.getString("pRODUCTDATE"));
			sellUtil.setRECEIVERINFOID(rs.getString("rECEIVERINFOID"));
			sellUtil.setREMARK(rs.getString("rEMARK"));
			sellUtil.setRETAILPRICE(rs.getBigDecimal("RETAILPRICE"));
			sellUtil.setSCHEDULEDAY(rs.getString("sCHEDULEDAY"));
			sellUtil.setSCHEDULEENDTIME(rs.getString("sCHEDULEENDTIME"));
			sellUtil.setSCHEDULESTARTTIME(rs.getString("sCHEDULESTARTTIME"));
			sellUtil.setSCHEDULETYPE(rs.getBigDecimal("sCHEDULETYPE"));
			sellUtil.setSELLERMESSAGE(rs.getString("sELLERMESSAGE"));
			sellUtil.setSELLERNICK(rs.getString("sELLERNICK"));
			sellUtil.setSENDERINFOID(rs.getString("sENDERINFOID"));
			sellUtil.setSENDFLAG(rs.getString("sENDFLAG"));
			sellUtil.setSERVICEFEE(rs.getBigDecimal("sERVICEFEE"));
			sellUtil.setSHOPNICK(rs.getString("sHOPNICK"));
			sellUtil.setSKUDISCOUNTAMOUNT(rs.getBigDecimal("sKUDISCOUNTAMOUNT"));
			sellUtil.setSOURCEORDERCODE(rs.getString("sOURCEORDERCODE"));
			sellUtil.setSOURCEPLATFORMCODE(rs.getString("sOURCEPLATFORMCODE"));
			sellUtil.setSOURCEPLATFORMNAME(rs.getString("sOURCEPLATFORMCODE"));
			sellUtil.setSTATUS(rs.getString("sTATUS"));
			sellUtil.setSUBSOURCEORDERCODE(rs.getString("sUBSOURCEORDERCODE"));
			sellUtil.setTOTALAMOUNT(rs.getBigDecimal("tOTALAMOUNT"));
			sellUtil.setTYPE(rs.getString("tYPE"));
			sellUtil.setWAREHOUSECODE(rs.getString("wAREHOUSECODE"));
			sellUtil.setUSERID(rs.getString("uSERID"));
			sellUtil.setCOMPANY(rs.getString("uSERID"));
			sellUtil.setNAME(rs.getString("nAME"));
			sellUtil.setZIPCODE(rs.getString("zIPCODE"));
			sellUtil.setTEL(rs.getString("tEL"));
			sellUtil.setMOBILE(rs.getString("mOBILE"));
			sellUtil.setEMAIL(rs.getString("eMAIL"));
			sellUtil.setCOUNTRYCODE(rs.getString("cOUNTRYCODE"));
			sellUtil.setPROVINCE(rs.getString("pROVINCE"));
			sellUtil.setCITY(rs.getString("city"));
			sellUtil.setAREA(rs.getString("aREA"));
			sellUtil.setTOWN(rs.getString("tOWN"));
			sellUtil.setDETAILADDRESS(rs.getString("dETAILADDRESS"));
			sellUtil.setCARNO(rs.getString("cARNO"));
			sellUtil.setCARDIDNO(rs.getString("cARDIDNO"));
			log.info("系统获取到转换为 ctl数据后的 ownercode："+sellUtil.getOWNERCODE());
		}
		rs.close();
		ctlstat.close();
	    ctlconn.close();
		return sellUtil;
	}
	
	/**
	 * 根据发运订单ID 获取明细信息
	 * @param deliveryOrderId
	 * @return
	 * @throws Exception
	 */
	public synchronized List<SellSoInfoUtil> getSellSoInfoUtilsById(SellSoInfoUtil sellSoInfoUtil) throws Exception{
		System.out.println("根据发运订单id获取明细信息"+sellSoInfoUtil);
		List<SellSoInfoUtil> sellSoInfoUtils = new ArrayList<SellSoInfoUtil>();
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String getSellSoInfoUtilSql = "SELECT * FROM ctl_soinfo_fhd_info t where t.deliveryOrderId=?";
		System.out.println(getSellSoInfoUtilSql+"\n"+sellSoInfoUtil.getDELIVERYORDERID());
		PreparedStatement ctlstat = ctlconn.prepareStatement(getSellSoInfoUtilSql);
		ctlstat.setString(1, sellSoInfoUtil.getDELIVERYORDERID());
		ResultSet rs = ctlstat.executeQuery();
		System.out.println("SQL 执行完成！");
		while(rs.next()){
			SellSoInfoUtil sellUtil = new SellSoInfoUtil();
			sellUtil.setACTUALPRICE(rs.getBigDecimal("aCTUALPRICE"));
			if(sellSoInfoUtil.getPLANQTY()==null){
				sellSoInfoUtil.setPLANQTY(new BigDecimal(0));
			}
			if(sellSoInfoUtil.getPLANQTY().equals(rs.getBigDecimal("PLANQTY"))&&sellSoInfoUtil.getITEMCODE().equals(rs.getString("ITEMCODE"))){
				sellUtil.setACTUALQTY(sellSoInfoUtil.getACTUALQTY());
			}else{
				sellUtil.setACTUALQTY(rs.getBigDecimal("ACTUALQTY"));
			}
			sellUtil.setAMOUNT(rs.getBigDecimal("aMOUNT"));
			sellUtil.setARAMOUNT(rs.getBigDecimal("aRAMOUNT"));
			sellUtil.setBATCHCODE(rs.getString("bATCHCODE"));
			sellUtil.setBUYERMESSAGE(rs.getString("bUYERMESSAGE"));
			sellUtil.setBUYERNICK(rs.getString("bUYERNICK"));
			sellUtil.setCONFIRMTYPE(rs.getBigDecimal("cONFIRMTYPE"));
			sellUtil.setCREATETIME(rs.getString("cREATETIME"));
			sellUtil.setDELIVERYORDERCODE(rs.getString("dELIVERYORDERCODE"));
			sellUtil.setDELIVERYORDERID(rs.getString("dELIVERYORDERID"));
			sellUtil.setDELIVERYTYPE(rs.getString("dELIVERYTYPE"));
			sellUtil.setDESTINATION(rs.getString("dESTINATION"));
			sellUtil.setDISCOUNTAMOUNT(rs.getBigDecimal("dISCOUNTAMOUNT"));
			sellUtil.setEXPIREDATE(rs.getString("eXPIREDATE"));
			sellUtil.setEXPRESSCODE(rs.getString("eXPRESSCODE"));
			sellUtil.setEXTCODE(rs.getString("eXTCODE"));
			sellUtil.setFREIGHT(rs.getBigDecimal("fREIGHT"));
			sellUtil.setGOTAMOUNT(rs.getBigDecimal("gOTAMOUNT"));
			sellUtil.setINSURANCEFLAG(rs.getString("iNSURANCEFLAG"));
			sellUtil.setINVENTORYTYPE(rs.getString("iNVENTORYTYPE"));
			sellUtil.setINVOICEINFOID(rs.getString("iNVOICEINFOID"));
			sellUtil.setITEMAMOUNT(rs.getBigDecimal("iTEMAMOUNT"));
			sellUtil.setITEMCODE(rs.getString("iTEMCODE"));
			sellUtil.setITEMID(rs.getString("iTEMID"));
			sellUtil.setITEMNAME(rs.getString("iTEMNAME"));
			sellUtil.setLOGISTICSAREACODE(rs.getString("lOGISTICSAREACODE"));
			sellUtil.setLOGISTICSCODE(rs.getString("lOGISTICSCODE"));
			sellUtil.setLOGISTICSNAME(rs.getString("lOGISTICSNAME"));
			sellUtil.setOPERATETIME(rs.getString("oPERATETIME"));
			sellUtil.setOPERATORCODE(rs.getString("oPERATORCODE"));
			sellUtil.setOPERATORNAME(rs.getString("oPERATORNAME"));
			sellUtil.setORDERCONFIRMTIME(rs.getString("oRDERCONFIRMTIME"));
			sellUtil.setORDERFLAG(rs.getString("oRDERFLAG"));
			sellUtil.setORDERLINEID(rs.getString("oRDERLINEID"));
			sellUtil.setORDERLINENO(rs.getString("oRDERLINENO"));
			sellUtil.setORDERTYPE(rs.getString("oRDERTYPE"));
			sellUtil.setOUTBIZCODE(rs.getString("oUTBIZCODE"));
			sellUtil.setOWNERCODE(ownerCodeDao.getCustomerIdByOwnerCode(rs.getString("oWNERCODE")));
			sellUtil.setPAYNO(rs.getString("pAYNO"));
			sellUtil.setPAYNO_DET(rs.getString("pAYNO_DET"));
			sellUtil.setPAYTIME(rs.getString("pAYTIME"));
			sellUtil.setPLACEORDERTIME(rs.getString("pLACEORDERTIME"));
			sellUtil.setPLANQTY(rs.getBigDecimal("pLANQTY"));
			sellUtil.setPREDELIVERYORDERCODE(rs.getString("pREDELIVERYORDERCODE"));
			sellUtil.setPREDELIVERYORDERID(rs.getString("pREDELIVERYORDERID"));
			sellUtil.setPRODUCECODE(rs.getString("pRODUCECODE"));
			sellUtil.setPRODUCTDATE(rs.getString("pRODUCTDATE"));
			sellUtil.setRECEIVERINFOID(rs.getString("rECEIVERINFOID"));
			sellUtil.setREMARK(rs.getString("rEMARK"));
			sellUtil.setRETAILPRICE(rs.getBigDecimal("RETAILPRICE"));
			sellUtil.setSCHEDULEDAY(rs.getString("sCHEDULEDAY"));
			sellUtil.setSCHEDULEENDTIME(rs.getString("sCHEDULEENDTIME"));
			sellUtil.setSCHEDULESTARTTIME(rs.getString("sCHEDULESTARTTIME"));
			sellUtil.setSCHEDULETYPE(rs.getBigDecimal("sCHEDULETYPE"));
			sellUtil.setSELLERMESSAGE(rs.getString("sELLERMESSAGE"));
			sellUtil.setSELLERNICK(rs.getString("sELLERNICK"));
			sellUtil.setSENDERINFOID(rs.getString("sENDERINFOID"));
			sellUtil.setSENDFLAG(rs.getString("sENDFLAG"));
			sellUtil.setSERVICEFEE(rs.getBigDecimal("sERVICEFEE"));
			sellUtil.setSHOPNICK(rs.getString("sHOPNICK"));
			sellUtil.setSKUDISCOUNTAMOUNT(rs.getBigDecimal("sKUDISCOUNTAMOUNT"));
			sellUtil.setSOURCEORDERCODE(rs.getString("sOURCEORDERCODE"));
			sellUtil.setSOURCEPLATFORMCODE(rs.getString("sOURCEPLATFORMCODE"));
			sellUtil.setSOURCEPLATFORMNAME(rs.getString("sOURCEPLATFORMCODE"));
			sellUtil.setSTATUS(rs.getString("sTATUS"));
			sellUtil.setSUBSOURCEORDERCODE(rs.getString("sUBSOURCEORDERCODE"));
			sellUtil.setTOTALAMOUNT(rs.getBigDecimal("tOTALAMOUNT"));
			sellUtil.setTYPE(rs.getString("tYPE"));
			sellUtil.setWAREHOUSECODE(rs.getString("wAREHOUSECODE"));
			sellUtil.setUSERID(rs.getString("uSERID"));
			sellUtil.setCOMPANY(rs.getString("uSERID"));
			sellUtil.setNAME(rs.getString("nAME"));
//			TODO 代码调试时注释掉，等三方联调时取消注释
			sellUtil.setZIPCODE(rs.getString("zIPCODE"));
			sellUtil.setTEL(rs.getString("tEL"));
			sellUtil.setMOBILE(rs.getString("mOBILE"));
			sellUtil.setEMAIL(rs.getString("eMAIL"));
			sellUtil.setCOUNTRYCODE(rs.getString("cOUNTRYCODE"));
			sellUtil.setPROVINCE(rs.getString("pROVINCE"));
			sellUtil.setCITY(rs.getString("city"));
			sellUtil.setAREA(rs.getString("aREA"));
			sellUtil.setTOWN(rs.getString("tOWN"));
			sellUtil.setDETAILADDRESS(rs.getString("dETAILADDRESS"));
			sellUtil.setCARNO(rs.getString("cARNO"));
			sellUtil.setCARDIDNO(rs.getString("cARDIDNO"));
			sellSoInfoUtils.add(sellUtil);
		}
		rs.close();
		ctlstat.close();
	    ctlconn.close();
	    System.out.println("根据发运订单id获取明细信息完成"+sellSoInfoUtil);
		return sellSoInfoUtils;
	}
	/**
	 * 获取所有发货单id-
	 * @return
	 * @throws Exception
	 */
	public synchronized List<String> getAlldeliveryOrderId() throws Exception{
		List<String> deliveryOrderIds = new ArrayList<String>();
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String getDeliveryIdSql = "SELECT distinct deliveryOrderId from ctl_soinfo_fhd";
		PreparedStatement ctlStat = ctlconn.prepareStatement(getDeliveryIdSql);
		ResultSet rs = ctlStat.executeQuery();
		while(rs.next()){
			String deliverOrderId = rs.getString("deliveryOrderId");
			deliveryOrderIds.add(deliverOrderId);
		}
		rs.close();
		ctlStat.close();
		ctlconn.close();
		return deliveryOrderIds;
	}
	/**
	 * 重置发货单标记为 SEND
	 * @param deliveryOrderId
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean upSellFlag(String deliveryOrderId) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String upSellFlagSql = "update tSellDeliveryOrderInfo t set t.sendflag='SEND',T.STATUS='ACCEPT' where t.deliveryOrderId=?";
		PreparedStatement ctlStat = ctlconn.prepareStatement(upSellFlagSql);
		System.out.println(upSellFlagSql+"\n"+deliveryOrderId);
		ctlStat.setString(1, deliveryOrderId);
		if(ctlStat.executeUpdate()==1){
			flag = true;
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
}
